package util

import (
	"database/sql"
	"fmt"

	_ "github.com/lib/pq"
)

const port string = "5432"
const dbname string = "postgres"
const user string = "postgres"
const driverName string = "postgres"
const password string = "123456"
const sslmode string = "disable"

var db *sql.DB

//获取*sql.DB
func GetDb() *sql.DB {
	return db
}

//初始化Db
func InitDb() {
	sourceString := "user=" + user + " password=" + password + " dbname=" + dbname + " sslmode=" + sslmode
	db2, err := sql.Open(driverName, sourceString)
	if err != nil {
		fmt.Println("From:InitDb:---", err.Error())
		return
	}
	db = db2
	fmt.Println("Init DB Success")
}

//关闭数据库
func CloseDb(db *sql.DB) {
	err := db.Close()
	if err != nil {
		fmt.Println("From: CloseDb:---", err.Error())
	}
}

//建表
func CreateTables() {
	_, _ = db.Exec(`delete from objects`)
	_, _ = db.Exec(`
		create table if not exists objects (
			id serial8 primary key,
			nickname varchar(200) not null,			
			icon varchar(500),	
			isUser bool not null,
			custom jsonb
		);
	`)
	_, _ = db.Exec(`select setval('objects_id_seq',10000);--设置起始值`)
	_, _ = db.Exec(`insert into objects(nickname,icon,isUser) values('dy','icon',true);`)
	_, _ = db.Exec(`insert into objects(nickname,icon,isUser) values('xl','icon1',true);`)
	_, _ = db.Exec(`insert into objects(nickname,icon,isUser) values('user3','icon3',true);`)
	_, _ = db.Exec(`insert into objects(nickname,icon,isUser) values('emb','icon2',false);`)
	_, _ = db.Exec(`insert into objects(nickname,icon,isUser) values('discuss','icon5',false);`)

	_, _ = db.Exec(`
		create table if not exists users (
			id int8 references objects(id) on delete cascade,
			password varchar(200) not null,
			isManager bool not null,
			primary key(id)
		);
		
	`)
	_, _ = db.Exec(`insert into users(id,password,isManager) values(10001,'123456',false);`)
	_, _ = db.Exec(`insert into users(id,password,isManager) values(10002,'123456',false);`)
	_, _ = db.Exec(`insert into users(id,password,isManager) values(10003,'123456',true);`)

	_, _ = db.Exec(`
		create table if not exists groups (
			id int8 references objects(id) on delete cascade,
			createTime timestamp with time zone default now(),
			primary key(id)
		);
	`)
	_, _ = db.Exec(`insert into groups(id) values(10004);`)
	_, _ = db.Exec(`insert into groups(id) values(10005);`)

	_, _ = db.Exec(`
		create table if not exists members (
			groupID int8 references objects(id) on delete cascade,
			userID int8 references objects(id) on delete cascade,
			primary key(groupID,userID)
		);
	`)
	_, _ = db.Exec(`insert into members(groupID,userID) values (10004,10001),(10004,10002);`)
	_, _ = db.Exec(`insert into members(groupID,userID) values (10005,10001),(10005,10002);`)

	_, _ = db.Exec(`
		create table if not exists messages (
			id serial8 primary key,
			content varchar(2000) not null,
			sender int8 references objects(id) on delete cascade,
			receiver int8 references objects(id) on delete cascade,
			createTime timestamp with time zone default now()
		);
	`)
	_, _ = db.Exec(`insert into messages(content,sender,receiver) values('dy:hello ficow!',10001,10002);`)
	_, _ = db.Exec(`insert into messages(content,sender,receiver) values('ficow:hello dy!',10002,10001);`)
	_, _ = db.Exec(`insert into messages(content,sender,receiver) values('user3:hello dy!',10003,10001);`)
	_, _ = db.Exec(`insert into messages(content,sender,receiver) values('dy:hello group!',10001,10004);`)
	_, _ = db.Exec(`insert into messages(content,sender,receiver) values('ficow:hello group!',10002,10004);`)
	_, _ = db.Exec(`insert into messages(content,sender,receiver) values('dy:hello discuss!',10001,10005);`)
	fmt.Println("create table success")
}
